Skip Headers

Oracle® Data Guard Concepts and Administration 10g Release 2 (10.2) Part Number B14239-04 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# 10 Using RMAN to Back Up and Restore Files

This chapter describes backup strategies using Oracle Recovery Manager utility (RMAN) with Data Guard and standby databases. RMAN can perform backups with minimal effect on the primary database and quickly recover from the loss of individual datafiles, or the entire database. RMAN and Data Guard can be used together to simplify the administration of a Data Guard configuration.

This chapter contains the following topics:

- Backup Procedure

- Effect of Switchovers, Failovers, and Control File Creation on Backups

- Additional Backup Situations

Note: Because a logical standby database is not a block-for-block copy of the primary database, you cannot use a logical standby database to back up the primary database.

## 10.1 Backup Procedure

In a standby environment, backing up datafiles and archived redo log files taken on the primary or standby system are usable on either system for recovery. Although some files such as the control file and SPFILE must be backed up on the primary database, the process of backing up datafiles and archived redo log files can be off-loaded to the standby system, to minimize the effect of backups on the production system.

Only those archived redo log files that were created by the standby instance can be backed up at the standby site. If there were any archived redo log files generated before the standby database was started, they must be backed up on the primary database. For example, if the first log sent from the primary database to the standby is log sequence 100 thread 1, then the backup of archived redo log files whose log sequence is less than 100 must be done on the primary database.

If the flash recovery area is configured, Oracle software deletes the files from the flash recovery area on an on-demand basis. The flash recovery area acts as disk cache for tape backups.

### 10.1.1 Using Disk as Cache for Tape Backup

The following instructions assume the flash recovery area is configured (as described in Section 5.2.3) and other RMAN persistent configurations are set. Perform the following steps:

1. On the primary database, issue the following RMAN commands to make a current backup of the control file and SPFILE, and back up files in the flash recovery area created by the primary instance to tape:

       BACKUP DEVICE TYPE DISK CURRENT CONTROLFILE;
       BACKUP RECOVERY AREA;

   Issue these commands (or use them in a script) every day or once a week, depending on how much application of redo data can be tolerated in the event of the loss of all current control files (see Section 10.2.4).

2. On the standby database, issue the following RMAN commands every day to roll forward a level 0 copy of the database:

       RECOVER COPY OF DATABASE WITH TAG 'OSS';
       BACKUP DEVICE TYPE DISK INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'OSS' DATABASE;
       BACKUP DEVICE TYPE DISK ARCHIVELOG ALL NOT BACKED UP 2 TIMES;
       BACKUP RECOVERY AREA;

   These commands apply the level 1 incremental backup taken a day before, create a new level 1 incremental backup, back up archived redo log files to the flash recovery area, and back up files created by the standby instance from the flash recovery area to tape.

### 10.1.2 Performing Backups Directly to Tape

If all backups are written directly to tape, configure the default device type to SBT using the RMAN `CONFIGURE DEFAULT DEVICE TYPE TO SBT command.`

On the primary database, use the following RMAN commands to back up the current control file and copy auto backups created by the primary instance to tape:

    BACKUP AS BACKUPSET CURRENT CONTROLFILE;
    BACKUP RECOVERY AREA;

Issue these commands every day or once a week, depending on how much application of redo data can be tolerated in the event of loss of all current control files (refer to Section 10.2.4).

Assuming that a complete database backup is taken every Sunday, the following commands can be issued on the standby database to take a level 0 database backup:

    BACKUP AS BACKUPSET INCREMENTAL LEVEL 0 DATABASE PLUS ARCHIVELOG NOT BACKED UP 2 TIMES;

On the other days of the backup cycle, run the following commands to create a level 1 incremental backup of the database and all archived redo log files that have not already been backed up 2 times:

    BACKUP AS BACKUPSET INCREMENTAL LEVEL 1 DATABASE PLUS ARCHIVELOG NOT BACKED UP 2 TIMES;

## 10.2 Effect of Switchovers, Failovers, and Control File Creation on Backups

All the archived redo log files that were generated after the last backup on the system where backups are done must be manually cataloged using the RMAN `CATALOG ARCHIVELOG` 'archivelog_name_complete_path' command after any of the following events:

- The primary or standby control file is re-created.

- The primary database role changes to standby after a switchover.

- The standby database role changes to primary after switchover or failover.

If the new archived redo log files are not cataloged, RMAN will not back them up.

The examples in the following sections assume you are restoring files from tape to the same system on which the backup was created. If you need to restore files to a different system, you may need to either change media configuration or specify different parameters on the RMAN channels during restore, or both. See the Media Management documentation for more information about how to access RMAN backups from different systems.

### 10.2.1 Recovery from Loss of Datafiles on the Primary Database

Issue the following RMAN commands to restore and recover datafiles. You must be connected to both the primary and recovery catalog databases.

    RESTORE DATAFILE n,m...;
    RECOVER DATAFILE n,m...;

Issue the following RMAN commands to restore and recover tablespaces. You must be connected to both the primary and recovery catalog databases.

    RESTORE TABLESPACE tbs_name1, tbs_name2, ...
    RECOVER TABLESPACE tbs_name1, tbs_name2, ...

### 10.2.2 Recovery from Loss of Datafiles on the Standby Database

To recover the standby database after the loss of one or more datafiles, you must restore the lost files to the standby database from the backup using the RMAN `RESTORE DATAFILE` command. If all the archived redo log files required for recovery of damaged files are accessible on disk by the standby database, restart Redo Apply.

If the archived redo log files required for recovery are not accessible on disk, use RMAN to recover the restored datafiles to an SCN/log sequence greater than the last log applied to the standby database, and then restart Redo Apply to continue the application of redo data, as follows:

1. Stop Redo Apply.

2. Determine the value of the `UNTIL_SCN` column, by issuing the following query:

       SQL> SELECT MAX(NEXT_CHANGE#)+1 UNTIL_SCN FROM V$LOG_HISTORY LH, V$DATABASE DB WHERE LH.RESETLOGS_CHANGE#=DB.RESETLOGS_CHANGE# AND LH.RESETLOGS_TIME = DB.RESETLOGS_TIME;
       UNTIL_SCN
       ------- ----------------
       967786

3. Issue the following RMAN commands to restore and recover datafiles on the standby database. You must be connected to both the standby and recovery catalog databases (use the `TARGET` keyword to connect to standby instance):

       RESTORE DATAFILE <n,m,...>;
       RECOVER DATABASE UNTIL SCN 967786;

   To restore a tablespace, use the RMAN `'RESTORE TABLESPACE` `tbs_name1, tbs_name2, ...`' command.

4. Restart Redo Apply.

### 10.2.3 Recovery from the Loss of a Standby Control File

Oracle software allows multiplexing of the standby control file. To ensure the standby control file is multiplexed, check the `CONTROL_FILES` initialization parameter, as follows:

    SQL> SHOW PARAMETER CONTROL_FILES
    NAME                                 TYPE        VALUE
     ------------------------------------ ----------- ------------------------------
    control_files                        string      <cfilepath1>,<cfilepath2>

If one of the multiplexed standby control files is lost or is not accessible, Oracle software stops the instance and writes the following messages to the alert log:

    ORA-00210: cannot open the specified controlfile
    ORA-00202: controlfile: '/ade/banand_hosted6/oracle/dbs/scf3_2.f'
    ORA-27041: unable to open file

You can copy an intact copy of the control file over the lost copy, then restart the standby instance using the following SQL statements:

    SQL> STARTUP MOUNT;
    SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

If all standby control files are lost, then you must create a new control file from the primary database, copy it to all multiplexed locations on the standby database, and restart the standby instance and Redo Apply. The created control file loses all information about archived redo log files generated before its creation. Because RMAN looks into the control file for the list of archived redo log files to back up, all the archived redo log files generated since the last backup must be manually cataloged.

### 10.2.4 Recovery from the Loss of the Primary Control File

Oracle software allows multiplexing of the control file on the primary database. If one of the control files cannot be updated on the primary database, the primary database instance is shut down automatically. As described in Section 10.2.3, you can copy an intact copy of the control file and restart the instance without having to perform restore or recovery operations.

If you lose all of your control files, you can choose among the following procedures, depending on the amount of downtime that is acceptable.

Create a new control file If all control file copies are lost, you can create a new control file using the `NORESETLOGS` option and open the database after doing media recovery. An existing standby database instance can generate the script to create a new control file by using the following statement:

    SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE NORESETLOGS;

Note that if the database filenames are different in the primary and standby databases, then you must edit the generated script to correct the filenames.

This statement can be used periodically to generate a control file creation script. If you are going to use control file creation as part of your recovery plan, then you should use this statement after any physical structure change, such as adding or dropping a datafile, tablespace, or redo log member.

The created control file loses all information about the archived redo log files generated before control file creation time. If archived redo log file backups are being performed on the primary database, all the archived redo log files generated since the last archived redo log file backup must be manually cataloged.

Recover using a backup control file If you are unable to create a control file using the previous procedure, then you can use a backup control file, perform complete recovery, and open the database with the `RESETLOGS` option.

To restore the control file and recover the database, issue the following RMAN commands after connecting to the primary instance (in `NOMOUNT` state) and catalog database:

    RESTORE CONTROLFILE;
    ALTER DATABASE MOUNT;
    RECOVER DATABASE;
    ALTER DATABASE OPEN RESETLOGS;

Beginning with Oracle Release 10.1.0, all the backups taken before a `RESETLOGS` operation can be used for recovery. Hence, it is not necessary to back up the database before making it available for production.

### 10.2.5 Recovery from the Loss of an Online Redo Log File

Oracle recommends multiplexing the online redo log files. The loss of all members of an online redo log group causes Oracle software to terminate the instance. If only some members of a log file group cannot be written, they will not be used until they become accessible. The views `V$LOGFILE` and `V$LOG` contain more information about the current status of log file members in the primary database instance.

When Oracle software is unable to write to one of the online redo log file members, the following alert messages are returned:

    ORA-00313: open failed for members of log group 1 of thread 1
    ORA-00312: online log 1 thread 1: '/ade/banand_hosted6/oracle/dbs/t1_log1.f'
    ORA-27037: unable to obtain file status
    SVR4 Error: 2: No such file or directory
    Additional information: 3

If the access problem is temporary due to a hardware issue, correct the problem and processing will continue automatically. If the loss is permanent, a new member can be added and the old one dropped from the group.

To add a new member to a redo log group, issue the following statement:

    SQL> ALTER DATABASE ADD LOGFILE MEMBER 'log_file_name' REUSE TO GROUP n

You can issue this statement even when the database is open, without affecting database availability.

If all members of an inactive group that has been archived are lost, the group can be dropped and re-created.

In all other cases (loss of all online log members for the current `ACTIVE` group, or an inactive group which has not yet been archived), you must fail over to the standby database. Refer to Chapter 7 for the failover procedure.

### 10.2.6 Incomplete Recovery of the Database

Incomplete recovery of the primary database is normally done in cases such as when the database is logically corrupted (by some user or an application) or when a tablespace or datafile was accidentally dropped from database.

Depending on the current database checkpoint SCN on the standby database instances, you can use one of the following procedures to perform incomplete recovery of the database. All the procedures are in order of preference, starting with the one that is the least time consuming.

Using Flashback Database Using Flashback Database is the recommended procedure when the Flashback Database feature is enabled on the primary database, none of the database files are lost, and the point-in-time recovery is greater than the oldest flashback SCN or the oldest flashback time. See Section 12.5 for the procedure to use Flashback Database to do point-in-time recovery.

Using the standby database instance This is the recommended procedure when the standby database is behind the desired incomplete recovery time, and Flashback Database is not enabled on the primary or standby databases:

1. Recover the standby database to the desired point in time.

       RECOVER DATABASE UNTIL TIME 'time';

   Alternatively, incomplete recovery time can be specified using the SCN or log sequence number:

       RECOVER DATABASE UNTIL SCN incomplete recovery SCN'
       RECOVER DATABASE UNTIL LOGSEQ incomplete recovery log sequence number THREAD thread number

2. Open the standby database in read-only mode to verify the state of database.

   If the state is not what is desired, use the LogMiner utility to look at the archived redo log files to find the right target time or SCN for incomplete recovery. Alternatively, you can start by recovering the standby database to a point that you know is before the target time, and then open the database in read-only mode to examine the state of the data. Repeat this process until the state of the database is verified to be correct. Note that if you recover the database too far (that is, past the SCN where the error occurred) you cannot return it to an earlier SCN.

3. Activate the standby database using the SQL `ALTER DATABASE ACTIVATE STANDBY DATABASE` statement. This converts the standby database to a primary database, creates a new resetlogs branch, and opens the database. See Section 8.4 to learn how the standby database reacts to the new reset logs branch.

Using the primary database instance If all of the standby database instances have already been recovered past the desired point in time and Flashback Database is enabled on the primary or standby database, then this is your only option.

Use the following procedure to perform incomplete recovery on the primary database:

1. Use LogMiner or another means to identify the time or SCN at which all the data in the database is known to be good.

2. Using the time or SCN, issue the following RMAN commands to do incomplete database recovery and open the database with the `RESETLOGS` option (after connecting to catalog database and primary instance that is in `MOUNT` state):

       RUN
       {
       SET UNTIL TIME 'time';
       RESTORE DATABASE;
       RECOVER DATABASE;
       }
       ALTER DATABASE OPEN RESETLOGS;

After this process, all standby database instances must be reestablished in the Data Guard configuration.

## 10.3 Additional Backup Situations

The following sections describe how to modify the backup procedures for other configurations, such as when the standby and primary databases cannot share backup files; the standby instance is only used to remotely archive redo log files; or the standby database filenames are different than the primary database.

### 10.3.1 Standby Databases Too Geographically Distant to Share Backups

In this case, the backups taken on a standby system are not easily accessible by the primary system or other standby systems. Perform a complete backup of the database on all systems to perform recovery operations. The flash recovery area can reside locally on the primary and standby systems (for example, the flash recovery area is not the same for the primary and standby databases).

In this scenario, you can still use the general strategies described in Section 10.2, with the following exceptions:

- Backup files created by RMAN must be tagged with the local system name, and with `RESTORE` operations that tag must be used to restrict RMAN from selecting backups taken on the same host. In other words, the `BACKUP` command must use the `TAG` node name option when creating backups; the `RESTORE` command must use the `FROM TAG` node name option; and the `RECOVER` command must use `FROM TAG` node name `ARCHIVELOG TAG` node name option.

- Disaster recovery of the standby site:

  1. Start the standby instance in the `NOMOUNT` state using the same parameter files with which the standby was operating earlier.

  2. Create a standby control file on the primary instance using the SQL `ALTER DATABASE CREATE STANDBY CONTROLFILE AS` filename statement, and use the created control file to mount the standby instance.

  3. Issue the following RMAN commands to restore and recover the database files:

         RESTORE DATABASE FROM TAG 'node name'
         RECOVER DATABASE FROM TAG 'node name' ARCHIVELOG TAG 'node name'

  4. Restart Redo Apply.

The standby instance will fetch the remaining archived redo log files as described in Section 5.8.

### 10.3.2 Standby Database Does Not Contain Datafiles, Used as a FAL Server

Use the same procedure described in Section 10.1, with the exception that the RMAN commands that back up database files cannot be run against the FAL server. The FAL server can be used as a backup source for all archived redo log files, thus off-loading backups of archived redo log files to the FAL server.

### 10.3.3 Standby Database File Names Are Different than Primary Database

If the database filenames are not the same on the primary and standby databases, the `RESTORE` and `RECOVER` commands you use will be slightly different. To obtain the actual datafile names on the standby database, query the `V$DATAFILE` view and specify the `SET NEWNAME` option for all the datafiles in the database:

    RUN
    {
    SET NEWNAME FOR DATAFILE 1 TO 'existing file location for file#1 from V$DATAFILE';
    SET NEWNAME FOR DATAFILE 2 TO 'existing file location for file#2 from V$DATAFILE';
    …
    …
     SET NEWNAME FOR DATAFILE n TO 'existing file location for file#n from V$DATAFILE';
     RESTORE {DATAFILE <n,m,…> | TABLESPACE tbs_name_1, 2, …| DATABASE;
    SWITCH DATAFILE ALL;
    RECOVER DATABASE {NOREDO};
    }

Similarly, the RMAN `DUPLICATE` command should also use the `SET NEWNAME` option to specify new filenames during standby database creation.

### 10.3.4 Deletion Policy for Archived Redo Log Files In Flash Recovery Areas

By default, archived redo log files in a flash recovery area that were backed up to a tertiary device or made obsolete (as defined by the RMAN retention policy) are eligible for deletion. The archived redo log files that are backed up or obsolete can eventually be deleted automatically to make space if the disk space in the flash recovery area becomes full. However, you can change this default deletion policy using the following RMAN command:

CONFIGURE ARCHIVELOG DELETION POLICY TO [CLEAR | NONE | APPLIED ON STANDBY];

This section describes the command qualifiers and provides examples for setting up a deletion policy. See Oracle Database Backup and Recovery Advanced User's Guide for more information about how Oracle software manages disk space in the flash recovery area.

Using the APPLIED ON STANDBY Clause

Use the `APPLIED ON STANDBY` clause so that archived redo log files that have been applied on all mandatory standby destinations will be deleted. The actions taken when you specify this clause are described in the following table:

When the `APPLIED ON STANDBY` clause is configured on. . . | Then, these files are eligible for deletion. . .
--- | ---
The primary database | Archived redo log files in the flash recovery area that were applied on all mandatory standby databases.
A standby database that has one or more mandatory cascading standby databases | Archived redo log files in the flash recovery area that were applied on all mandatory cascading standby databases.
A standby database that has no cascading standby databases | Archived redo log files in the flash recovery area that were applied on the standby database.

See Appendix E for more information about cascaded destinations.

Using the CLEAR Clause

Use the `CLEAR` clause to disable the deletion policy that was previously set up with the RMAN `CONFIGURE ARCHIVELOG DELETION POLICY` command. The Oracle database will resume the default deletion policy behavior, which is to delete archived redo log files that are backed up or obsolete to make space if disk space in the flash recovery area becomes full.

Using the NONE Clause

Use the `NONE` clause so that archived redo logs in flash recovery area that were backed up or obsolete as per the RMAN retention policy are eligible for deletion. This is the default configuration. Archived redo log files that are backed up or obsolete are deleted to make space if the disk space in the flash recovery area becomes full.

Examples of the CONFIGURE ARCHIVELOG DELETION POLICY Command

When backups of archived redo log files are taken on the standby database:

1. Issue the following command on the primary database:

       CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

2. Issue the following command on the standby database:

       CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;

When backups of archived redo log files are taken on the primary database:

1. Issue the following command on the standby database:

       CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

2. Issue the following command on the primary database:

       CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;

#### 10.3.4.1 Reconfiguring the Deletion Policy After a Role Transition

After a switchover or failover, you may need to reissue the RMAN `CONFIGURE ARCHIVELOG DELETION POLICY` command on each database. If the backup site for archived redo log files remains the same, then do nothing. Otherwise, you must switch the archivelog deletion policy by issuing the `CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY` statement on the database where backups are not taken, and issuing the `CONFIGURE ARCHIVELOG DELETION POLICY TO NONE` statement on the database where backups are taken.

#### 10.3.4.2 Viewing the Current Deletion Policy

To see the current setting (`APPLIED ON STANDBY`, `CLEAR`, `NONE`) for a database, issue the following query:

    SELECT NAME, VALUE FROM V$RMAN_CONFIGURATION WHERE
    NAME LIKE '%ARCHIVELOG DELETION POLICY%';

    NAME                              VALUE
    -----------------------------     --------------
    ARCHIVELOG DELETION POLICY        TO APPLIED ON STANDBY

You can also find the existing configuration using the RMAN `SHOW ARCHIVELOG DELETION POLICY` command:

    RMAN> SHOW ARCHIVELOG DELETION POLICY
    RMAN configuration parameters are:
    CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;